﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Transactions;

namespace ImportSales.Process
{
    public class UploadSalesIntoDb
    {
        int Arch_TRANSACTION_CODEIndex = 0;
        int Arch_SALESMANIndex = 0;
        int Arch_POINT_OF_SALES_CODEIndex = 0;       
        int Arch_DATE_OF_SALEIndex = 0;
        int Arch_INVOICE_NUMBERIndex = 0;
        int Arch_PAYMENT_METHODIndex = 0;
        int Arch_PAYMENT_CURRENCYIndex = 0;
        int Arch_COUNTRY_CURRENCYIndex = 0;
        int Arch_ITEM_QUANTITYIndex = 0;
        int Arch_ITEM_CODEIndex = 0;
        int Arch_ITEM_DESCRIPTIONIndex = 0;
        int Arch_ITEM_UNIT_PRICEIndex = 0;
        int Arch_ITEM_AMOUNTIndex = 0;
        int Arch_CUSTOMER_NUMBERIndex = 0;

        public decimal StartLineNumber = 0;
        public decimal MaxLinesToCommit = 100000; // cada este numero de lineas commitea y reconecta        

        public UploadSalesIntoDb()
        {
            int i = 0;
            Arch_TRANSACTION_CODEIndex = i++;
            Arch_SALESMANIndex = i++;
            Arch_POINT_OF_SALES_CODEIndex = i++;
            Arch_DATE_OF_SALEIndex = i++;
            Arch_INVOICE_NUMBERIndex = i++;
            Arch_PAYMENT_METHODIndex = i++;
            Arch_PAYMENT_CURRENCYIndex = i++;
            Arch_COUNTRY_CURRENCYIndex = i++;
            Arch_ITEM_QUANTITYIndex = i++;
            Arch_ITEM_CODEIndex = i++;
            Arch_ITEM_DESCRIPTIONIndex = i++;
            Arch_ITEM_UNIT_PRICEIndex = i++;
            Arch_ITEM_AMOUNTIndex = i++;
            Arch_CUSTOMER_NUMBERIndex = i++;
        }

        public string InputFilePath { get; set; }

        public string ConnectionString { get; set; }

        public int EmpresaId { get; set; }

        public delegate void Notificar(string mensaje);

        public event Notificar Notify;

        protected void OnNotificar(string mensaje)
        {
            if (Notify != null)
            {
                Notify(mensaje);
            }
        }

        public void DoImport()
        {
            int i = 0;

            //CurrentLine = 0;
            bool keepGoing = true;     
            decimal currentLineNumber = StartLineNumber;

            // Se corta la conexion y da timeout seguido
            while (keepGoing)
            {
                try
                {
                    using (DbConnection conn = new SqlConnection(ConnectionString))
                    {
                        OnNotificar("Abriendo conexión a la base");
                        conn.Open();

                        EjecutarComandoInsert(ref i, ref currentLineNumber, conn, ref keepGoing);

                        conn.Close();
                    }
                }
                catch (Exception ex)
                {
                    OnNotificar(string.Format("Linea Archivo: {0}", currentLineNumber));
                    string message = GetExceptionMessage(ex);
                    string stack = GetExceptionStackTrace(ex);

                    OnNotificar(message + stack);
                   
                    keepGoing = false;                    
                }
            }
        }

        private void EjecutarComandoInsert(ref int i, ref decimal currentLineNumber, DbConnection conn, ref bool keepGoing)
        {
            string line;
            DbTransaction dbTransaction = null;

            string lineOfFileForException = string.Empty;
            decimal readRow = 0;

            try
            {
                using (DbCommand dbCommand = conn.CreateCommand())
                {
                    dbCommand.Transaction = dbTransaction;
                    dbCommand.CommandText = "insert into Ventas ([EmpresaCodigoCrm],[EmpresaCodigoExterno],[TRANSACTION_CODE],[SALESMAN],[POINT_OF_SALES_CODE],[DATE_OF_SALE],[DATE_OF_SALE_YEAR],[DATE_OF_SALE_MONTH],[DATE_OF_SALE_DAY],[INVOICE_NUMBER],[TALONARIO],[PREFIJO],[PAYMENT_METHOD],[PAYMENT_CURRENCY],[COUNTRY_CURRENCY],[ITEM_QUANTITY],[ITEM_CODE],[ITEM_COLOR],[ITEM_SIZE],[ITEM_DESCRIPTION],[ITEM_UNIT_PRICE],[ITEM_AMOUNT],[CUSTOMER_NUMBER],[ContactoId], Temporada) " +
                                            "values (@EmpresaId, null, @TRANSACTION_CODE,@SALESMAN,@POINT_OF_SALES_CODE, @DATE_OF_SALE, datepart(year, @DATE_OF_SALE), datepart(month,@DATE_OF_SALE), datepart(day,@DATE_OF_SALE), @INVOICE_NUMBER, null, null, @PAYMENT_METHOD,@PAYMENT_CURRENCY,@COUNTRY_CURRENCY,@ITEM_QUANTITY,@ITEM_CODE, null, null, rtrim(ltrim(@ITEM_DESCRIPTION)),@ITEM_UNIT_PRICE,@ITEM_AMOUNT,@CUSTOMER_NUMBER, null);";

                    //dbCommand.Transaction = dbTransaction;                            
                    List<DbParameter> parameterKeys = new List<DbParameter>();

                    #region parametros
                    i = 0;
                    int EmpresaCodigoCrmIndex = i++;
                    DbParameter parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@EmpresaId";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int TRANSACTION_CODEIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@TRANSACTION_CODE";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int SALESMANIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@SALESMAN";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int POINT_OF_SALES_CODEIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@POINT_OF_SALES_CODE";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int DATE_OF_SALEIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@DATE_OF_SALE";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int INVOICE_NUMBERIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@INVOICE_NUMBER";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int PAYMENT_METHODIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@PAYMENT_METHOD";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int PAYMENT_CURRENCYIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@PAYMENT_CURRENCY";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int COUNTRY_CURRENCYIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@COUNTRY_CURRENCY";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int ITEM_QUANTITYIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@ITEM_QUANTITY";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int ITEM_CODEIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@ITEM_CODE";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int ITEM_DESCRIPTIONIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@ITEM_DESCRIPTION";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int ITEM_UNIT_PRICEIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@ITEM_UNIT_PRICE";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int ITEM_AMOUNTIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@ITEM_AMOUNT";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int CUSTOMER_NUMBERIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@CUSTOMER_NUMBER";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

#endregion

                    OnNotificar(string.Format("Abriendo arhivo de ventas {0}", InputFilePath));
                    OnNotificar(string.Format("Ejecutando lineas desde {0} ", currentLineNumber));
                    bool comiteado = false;
                    
                    decimal insertedQuantity = 0;
                    using (System.IO.StreamReader file = new System.IO.StreamReader(InputFilePath))
                    {                        
                        while ((line = file.ReadLine()) != null)
                        {
                            if (readRow >= currentLineNumber)
                            {
                                if (dbTransaction == null)
                                {
                                    dbTransaction = conn.BeginTransaction();
                                    dbCommand.Transaction = dbTransaction;
                                }

                                currentLineNumber = readRow;
                                lineOfFileForException = line;
                                string[] dataRow = line.Split(';');

                                parameterKeys[EmpresaCodigoCrmIndex].Value = EmpresaId;
                                parameterKeys[TRANSACTION_CODEIndex].Value = dataRow[Arch_TRANSACTION_CODEIndex];
                                parameterKeys[SALESMANIndex].Value = dataRow[Arch_SALESMANIndex];
                                parameterKeys[POINT_OF_SALES_CODEIndex].Value = dataRow[Arch_POINT_OF_SALES_CODEIndex];

                                DateTime fecha = DateTime.ParseExact(dataRow[Arch_DATE_OF_SALEIndex], "dd/MM/yyyy", null);
                                parameterKeys[DATE_OF_SALEIndex].Value = fecha;

                                parameterKeys[INVOICE_NUMBERIndex].Value = dataRow[Arch_INVOICE_NUMBERIndex];
                                parameterKeys[PAYMENT_METHODIndex].Value = dataRow[Arch_PAYMENT_METHODIndex];
                                parameterKeys[PAYMENT_CURRENCYIndex].Value = dataRow[Arch_PAYMENT_CURRENCYIndex];
                                parameterKeys[COUNTRY_CURRENCYIndex].Value = dataRow[Arch_COUNTRY_CURRENCYIndex];
                                parameterKeys[ITEM_QUANTITYIndex].Value = dataRow[Arch_ITEM_QUANTITYIndex];
                                parameterKeys[ITEM_CODEIndex].Value = dataRow[Arch_ITEM_CODEIndex];
                                parameterKeys[ITEM_DESCRIPTIONIndex].Value = dataRow[Arch_ITEM_DESCRIPTIONIndex];
                                parameterKeys[ITEM_UNIT_PRICEIndex].Value = dataRow[Arch_ITEM_UNIT_PRICEIndex];
                                parameterKeys[ITEM_AMOUNTIndex].Value = dataRow[Arch_ITEM_AMOUNTIndex];
                                parameterKeys[CUSTOMER_NUMBERIndex].Value = dataRow[Arch_CUSTOMER_NUMBERIndex];
                                

                                dbCommand.ExecuteNonQuery();
                                insertedQuantity++;
                            }

                            if (insertedQuantity >= MaxLinesToCommit)
                            {
                                if (dbTransaction != null)
                                {
                                    dbTransaction.Commit();
                                }

                                comiteado = true;
                                break;
                            }

                            readRow++;
                        }

                        file.Close();

                        if (!comiteado)
                        {
                            OnNotificar(string.Format("Ejecutando commit. Linea {0}", currentLineNumber));
                            if (dbTransaction != null)
                            {
                                dbTransaction.Commit();
                            }

                            comiteado = true;
                            keepGoing = false;
                        }
                        // tScope.Complete();
                        OnNotificar(string.Format("Se procesaron {0} lineas del archivo", insertedQuantity));
                    }
                }
            }
            catch
            {
                OnNotificar("Ejecutando rollback");
                OnNotificar(string.Format("Fila: {0}", readRow));
                OnNotificar(string.Format("Linea Archivo: {0}", lineOfFileForException));
                if (dbTransaction != null)
                {
                    dbTransaction.Rollback();
                }

                throw;
            }           
        }

        public void DoSincSalesCustomers()
        {
            using (DbConnection conn = new SqlConnection(ConnectionString))
            {
                OnNotificar("Abriendo conexión a la base para ejecutar stored de sincronizacion de ventas");
                conn.Open();

                DbTransaction dbTransaction = conn.BeginTransaction();

                try
                {
                    using (DbCommand dbCommand = conn.CreateCommand())
                    {
                        dbCommand.Transaction = dbTransaction;
                        dbCommand.CommandText = "Sincronizar_VentasLacoste_BR";
                        dbCommand.CommandType = System.Data.CommandType.StoredProcedure;

                        OnNotificar("Ejecutando stored de sincronizacion de ventas");
                        dbCommand.ExecuteNonQuery();

                        OnNotificar("Ejecutando stored de sincronizacion de ventas - commit");
                        dbTransaction.Commit();
                    }
                }
                catch
                {
                    if (dbTransaction != null)
                    {
                        OnNotificar("Ejecutando stored de sincronizacion de ventas - rollback");
                        dbTransaction.Rollback();
                    }

                    throw;
                }

                conn.Close();
            }
        }

        public static string GetExceptionMessage(Exception ex)
        {
            StringBuilder builder = new StringBuilder();

            Exception current = ex;
            while (current != null)
            {
                builder.Append(current.Message);
                current = current.InnerException;
            }

            return builder.ToString();
        }

        public static string GetExceptionStackTrace(Exception ex)
        {
            StringBuilder builder = new StringBuilder();

            Exception current = ex;
            while (current != null)
            {
                builder.Append(current.StackTrace);
                current = current.InnerException;
            }

            return builder.ToString();
        }
    }
}
